package cn.ptu.pmaa.mapper;


import cn.ptu.pmaa.entities.Appintment;
import cn.ptu.pmaa.entities.ClassRoom;
import cn.ptu.pmaa.entities.Student;
import cn.ptu.pmaa.entities.YyClassRoom;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

public interface StudentMapper {

//    @Select("SELECT jsid,jsld,jsfh,jszws-shzws-COUNT(*) as syzws \n" +
//            "FROM classroom\n" +
//            "LEFT JOIN appintment\n" +
//            "ON classroom.jsid=appintment.yydjsid\n" +
//            "WHERE sfqy=1 AND sfkfgxs=1 AND state=1\n" +
//            "AND yyrq = #{date} \n"+
//            "AND (yykssj NOT BETWEEN #{fromtime} AND #{totime} )\n" +
//            "AND (yyjssj NOT BETWEEN #{fromtime} AND #{totime} )\n" +
//            "GROUP BY appintment.yydjsid")
    @Select("SELECT c.jsid,c.jsld,c.jsfh,c.jszws,c.shzws,c.jszws-c.shzws-COUNT(a.yydjsid) AS syzws\n" +
            "FROM classroom c\n" +
            "LEFT JOIN appintment a ON c.jsid=a.yydjsid \n" +
            "AND a.yyrq = #{date} \n" +
            "AND ((a.yykssj BETWEEN #{fromtime} AND #{totime} ) OR (a.yyjssj BETWEEN #{fromtime} AND #{totime} ))\n" +
            "WHERE c.sfqy=1 AND c.sfkfgxs=1\n" +
            "GROUP BY c.jsid\n")
    public List<YyClassRoom> getSiteByTime(@Param("fromtime") String fromtime, @Param("totime") String totime, @Param("date") String date);


    @Insert("INSERT INTO appintment(yydjsid,yyrq,yykssj,yyjssj,yyrid,yyrjs) " +
            "VALUES(#{yydjsid},#{yyrq},#{yykssj},#{yyjssj},#{yyrid},#{yyrjs})")
    public int insertAppintment(Appintment appintment);

    @Select("SELECT * FROM appintment WHERE yyrid=#{yyrid}")
    public List<Appintment> getAppintment(@Param("yyrid") String id);

    @Select("SELECT jsld,jsfh FROM classroom WHERE jsid=#{jsid}")
    public ClassRoom getClassroomById(@Param("jsid") String id);

    @Select("SELECT yyrid FROM appintment WHERE yyid=#{id}")
    public String getUserIdByYyid(@Param("id") String id);

    @Update("UPDATE appintment SET state = 0 WHERE yyid=#{id}")
    public int setAppiState(@Param("id") String id);

    @Update("UPDATE appintment SET yyjssj=#{time} WHERE yyid=#{id}")
    public int setYyjssj(@Param("id") String id,@Param("time") String time);

    @Update("UPDATE student SET studentname=#{studentName},studentsex=#{studentSex},studenttel=#{studentTel},studentemail=#{studentEMail}" +
            " WHERE studentid=#{studentId}")
    public int setStudentInfo(Student student);

}
